
Imports System
Imports System.Collections
Imports System.Data.SqlClient
Imports System.Diagnostics
Imports _2bMatesSite.Common

Namespace _2bMatesSite.BO
	''' <summary>
	''' Summary description for Groups.
	''' </summary>
	Public Class Groups

		' Find a group by it's ID
		' returns null if the group was not found
		Public Shared Function GetGroupByID(ByVal grpID As Integer) As Group
			Dim cmd As New SqlCommand("SELECT * FROM Groups WHERE ID=@grpID")
			cmd.Parameters.Add("@grpID", grpID)

			Return GetGroupByCmd(cmd)
		End Function

		Public Shared Function LoadPopularGroups() As ArrayList
			Dim sql As String = "SELECT TOP " + Constants.GROUPS_POPULAR_MAX + " * FROM vGroupMembers" + Environment.NewLine
			sql += "" & Chr(9) & "ORDER BY Members Desc, Name Asc"

			Dim cmd As New SqlCommand(sql)

			Return ListGroupsByCmd(cmd)
		End Function

		Public Shared Function LoadCategoryGroups(ByVal catID As Integer, ByVal sortByValue As String) As ArrayList
			Dim cmd As SqlCommand = Nothing

			Select Case sortByValue
				Case Constants.GROUP_SORT_BY_POPULARITY
					Dim sql As String = "SELECT * FROM vGroupMembers" + Environment.NewLine
					sql += "" & Chr(9) & "WHERE CatID=@catID" + Environment.NewLine
					sql += "" & Chr(9) & "ORDER BY Members Desc, Name Asc"

					cmd = New SqlCommand(sql)
					Exit Select
				Case Constants.GROUP_SORT_BY_NAME

					cmd = New SqlCommand("SELECT * FROM Groups WHERE CatID=@catID ORDER BY Name ASC ")
					Exit Select
				Case Constants.GROUP_SORT_BY_MOST_RECENT

					cmd = New SqlCommand("SELECT * FROM Groups WHERE CatID=@catID ORDER BY [Date] DESC ")
					Exit Select
			End Select

			cmd.Parameters.Add("@catID", catID)

			Return ListGroupsByCmd(cmd)
		End Function

		' Get all groups, where the given user is the moderator
		Public Shared Function MyModeratedGroups(ByVal userID As Integer) As ArrayList
			Dim cmd As New SqlCommand("SELECT * FROM Groups WHERE UID=@userID ORDER BY Name ASC")
			cmd.Parameters.Add("@userID", userID)

			Return ListGroupsByCmd(cmd)
		End Function

		Public Shared Function MyGroups(ByVal userID As Integer, ByVal sortByValue As String) As ArrayList
			Dim cmd As SqlCommand = Nothing

			Select Case sortByValue
				Case Constants.GROUP_SORT_BY_POPULARITY
					Dim sql As String = "SELECT * FROM vMyGroups" + Environment.NewLine
					sql += "" & Chr(9) & "WHERE MemberID=@userID" + Environment.NewLine
					sql += "" & Chr(9) & "ORDER BY Members Desc, Name Asc"

					cmd = New SqlCommand(sql)
					Exit Select
				Case Constants.GROUP_SORT_BY_NAME

					cmd = New SqlCommand("SELECT * FROM vMyGroups WHERE MemberID=@userID ORDER BY Name ASC ")
					Exit Select
				Case Constants.GROUP_SORT_BY_MOST_RECENT

					cmd = New SqlCommand("SELECT * FROM vMyGroups WHERE MemberID=@userID ORDER BY [Date] DESC ")
					Exit Select
			End Select

			cmd.Parameters.Add("@userID", userID)

			Return ListGroupsByCmd(cmd)
		End Function

		Public Shared Function MyMemberGroups(ByVal userID As Integer) As ArrayList
			Dim cmd As SqlCommand = Nothing

			Dim sql As String = "SELECT * FROM vMemberGroups" + Environment.NewLine
			sql += "" & Chr(9) & "WHERE MemberID=@userID" + Environment.NewLine
			sql += "" & Chr(9) & "ORDER BY Name Asc"

			cmd = New SqlCommand(sql)

			cmd.Parameters.Add("@userID", userID)

			Return ListGroupsByCmd(cmd)
		End Function

		Public Shared Function GetFeaturedGroup() As Group
			Dim cmd As New SqlCommand("SELECT TOP 1 * FROM Groups ORDER BY FeatureGroup DESC, Date DESC")
			Return GetGroupByCmd(cmd)
		End Function

		Public Shared Sub SetFeaturedGroup(ByVal grpID As Integer)
			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As New SqlCommand("UPDATE Groups SET FeatureGroup = 0;")
				cmd.Connection = conn

				If grpID <> 0 Then
					cmd = New SqlCommand("UPDATE Groups SET FeatureGroup = 0; UPDATE Groups SET FeatureGroup = 1 where id = @grpID")
					cmd.Connection = conn

					cmd.Parameters.Add("@grpID", grpID)
				End If

				cmd.ExecuteNonQuery()
			Finally
				conn.Close()
			End Try
		End Sub


		' Returns the groups found by the search criteria
		Public Shared Function FindGroups(ByVal filter As GroupFilter) As ArrayList
			Dim groups As New ArrayList()

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				Dim cmd As SqlCommand = GetFindGroupsCMD(filter)
				cmd.Connection = conn

				Dim dr As SqlDataReader = cmd.ExecuteReader()
				While dr.Read()
					Dim grp As New Group(dr)
					groups.Add(grp)
				End While
			Finally
				conn.Close()
			End Try

			Return groups
		End Function

		' Find a group by it's name and CatID
		' returns null if the group was not found
		Public Shared Function GetGroupByCatIDName(ByVal catID As Integer, ByVal name As String) As Group
			Dim cmd As New SqlCommand("SELECT * FROM Groups WHERE CatID=@CatID AND Name=@Name")
			cmd.Parameters.Add("@CatID", catID)
			cmd.Parameters.Add("@Name", name)

			Return GetGroupByCmd(cmd)
		End Function

		' Composes the SqlCommand with the SQL string that matches the groups,
		' according to the given search criteria
		Protected Shared Function GetFindGroupsCMD(ByVal filter As GroupFilter) As SqlCommand
			Dim sql As String = "SELECT * FROM vGroupMembers" + Environment.NewLine

			' Put the conditions here
			Dim tempCondition As String = ""

			If filter.countryID >= 0 Then
				tempCondition += " CountryID = " + filter.countryID.ToString() + Environment.NewLine
			End If

			If filter.categoryID >= 0 Then
				If tempCondition.Length > 0 Then
					tempCondition += " AND "
				End If
				tempCondition += " CatID = " + filter.categoryID.ToString() + Environment.NewLine
			End If

			If filter.keywords <> Nothing AndAlso filter.keywords.Length > 0 Then
				If tempCondition.Length > 0 Then
					tempCondition += " AND "
				End If
				If filter.searchByKeyword AndAlso Not filter.searchByName Then
					tempCondition += "(About " + Utils.GetLikeSQL(filter.keywords) + Environment.NewLine
					tempCondition += " OR " + "Activities " + Utils.GetLikeSQL(filter.keywords) + Environment.NewLine
					tempCondition += " OR " + "Contacts " + Utils.GetLikeSQL(filter.keywords) + Environment.NewLine
					tempCondition += " OR " + "Charity " + Utils.GetLikeSQL(filter.keywords) + " )" + Environment.NewLine
ElseIf Not filter.searchByKeyword AndAlso filter.searchByName Then
					tempCondition += " Name " + Utils.GetLikeSQL(filter.keywords) + Environment.NewLine
				Else
					tempCondition += "(Name " + Utils.GetLikeSQL(filter.keywords) + Environment.NewLine
					tempCondition += " OR " + "About " + Utils.GetLikeSQL(filter.keywords) + Environment.NewLine
					tempCondition += " OR " + "Activities " + Utils.GetLikeSQL(filter.keywords) + Environment.NewLine
					tempCondition += " OR " + "Contacts " + Utils.GetLikeSQL(filter.keywords) + Environment.NewLine
					tempCondition += " OR " + "Charity " + Utils.GetLikeSQL(filter.keywords) + " )" + Environment.NewLine
				End If
			End If
			If filter.city <> Nothing AndAlso filter.city.Length > 0 Then
				If tempCondition.Length > 0 Then
					tempCondition += " AND "
				End If
				tempCondition += " City " + Utils.GetLikeSQL(filter.city) + Environment.NewLine
			End If
			If filter.zip <> Nothing AndAlso filter.zip.Length > 0 Then
				If tempCondition.Length > 0 Then
					tempCondition += " AND "
				End If
				tempCondition += " ZipCode " + Utils.GetLikeSQL(filter.zip) + Environment.NewLine
			End If
			If tempCondition.Length > 0 Then
				sql += "" & Chr(9) & "WHERE " + Environment.NewLine
				sql += tempCondition
			End If

			If filter.orderBy = GroupFilter.OrderBy.members Then
				sql += "ORDER BY Members Desc" + Environment.NewLine
ElseIf filter.orderBy = GroupFilter.OrderBy.recentlyAdded Then
				sql += "ORDER BY [Date] Desc" + Environment.NewLine
			Else
				sql += "ORDER BY Name " + Environment.NewLine
			End If

			Dim cmd As New SqlCommand(sql)
			Return cmd
		End Function


		' Get random groups which have entered bands
		Public Shared Function FindRandomGroupsWithBands(ByVal grpCount As Integer) As ArrayList
			Dim allGroups As New ArrayList()

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				' Build the users list
				Dim cmdSelect As New SqlCommand(GetGroupsWithBandsSQL())
				cmdSelect.Connection = conn

				Dim dr As SqlDataReader = cmdSelect.ExecuteReader()

				' Read the users array
				While dr.Read()
					Dim g As New Group(dr)
					allGroups.Add(g)
				End While


				dr.Close()
			Catch generatedExceptionName As Exception
				Throw
			Finally
				conn.Close()
			End Try

			'
			' Not choose a number of random users among those found
			'
			Dim allGroupsCount As Integer = allGroups.Count
			grpCount = Math.Min(grpCount, allGroupsCount)

			Dim groups As New ArrayList()
			Dim hash As New Hashtable()
			Dim rnd As New Random()

			Dim foundCnt As Integer = 0
			While foundCnt < grpCount
				Dim gr As Group = DirectCast(allGroups(rnd.[Next](allGroupsCount)), Group)

				If Not hash.Contains(gr.ID) Then
					groups.Add(gr)
					hash(gr.ID) = gr.ID
					System.Math.Max(System.Threading.Interlocked.Increment(foundCnt),foundCnt - 1)
				End If
			End While

			Return groups
		End Function

		' Get groups with bands

		Protected Shared Function GetGroupsWithBandsSQL() As String
			Dim sql As String = "SELECT     dbo.Groups.* " + Environment.NewLine + "FROM         dbo.Groups INNER JOIN " + Environment.NewLine + "dbo.GroupBands ON dbo.Groups.ID = dbo.GroupBands.GroupID " + Environment.NewLine + "WHERE     (dbo.Groups.DefaultPictureID > 0) AND ((dbo.Groups.SongURL1 <> '') OR (dbo.Groups.SongURL2 <> '') OR (dbo.Groups.SongURL3 <> ''))"
			Return sql
		End Function

		' Retrieve the group using the given SQL command object
		' return null if no group found
		' NOTE: asserts that only one group is selected
		'		for more groups use another method
		Protected Shared Function GetGroupByCmd(ByVal cmd As SqlCommand) As Group
			Dim grp As Group = Nothing

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				cmd.Connection = conn
				Dim dr As SqlDataReader = cmd.ExecuteReader()

				If dr.Read() Then
					grp = New Group(dr)
					Debug.Assert(Not dr.Read())
				End If

				dr.Close()
			Finally
				conn.Close()
			End Try

			Return grp
		End Function

		' Retrieve a group (Group) list using the given SQL command object
		' return null if no groups are found
		Protected Shared Function ListGroupsByCmd(ByVal cmd As SqlCommand) As ArrayList
			Dim list As New ArrayList()

			Dim conn As SqlConnection = DBManager.GetUnmanagedConnection()

			Try
				cmd.Connection = conn
				Dim dr As SqlDataReader = cmd.ExecuteReader()

				While dr.Read()
					Dim grp As New Group(dr)
					list.Add(grp)
				End While

				dr.Close()
			Finally
				conn.Close()
			End Try

			Return list
		End Function

		' Send a notification of a updated blog comment
		Public Shared Sub SendBlogUpdateNotification(ByVal grp As Group, ByVal be As GroupBlog)
			Dim toUser As User = Users.GetUserByID(grp.Uid)
			If Not toUser.EnableGroupEmailNotify Then
				Return
			End If

			Dim from As String = Constants.MESSAGES_EMAIL
			Dim [to] As String = "<" + toUser.Email + ">"
			Dim subj As String = Constants.NOTIFY_UPDATED_GROUP_BLOG_SUBJECT

			' Blog sender user name
			Dim blogSender As User = be.Sender
			Dim body As String = Constants.NOTIFY_UPDATED_GROUP_BLOG_BODY.Replace(Constants.TXT_USER_NAME_TAG, blogSender.Name)

			' Blog link
			body = body.Replace(Constants.TXT_BLOG_LINK_TAG, ComposeBlogLink(be) + "&" + Users.ComposeURLLoginParams(toUser))

			' No SPAM disclaimer
			body = body.Replace(Constants.TXT_DISCLAIMER_TEXT_TAG, Users.ComposeNoSPAMDisclaimer(toUser))

			' Do send the mail
			Utils.SendMail(from, [to], subj, body)
		End Sub

		' Send a notification of a new blog comment
		Public Shared Sub SendBlogNotification(ByVal grp As Group, ByVal be As GroupBlog)
			Dim toUser As User = Users.GetUserByID(grp.Uid)

			If Not toUser.EnableGroupEmailNotify Then
				Return
			End If

			Dim from As String = Constants.MESSAGES_EMAIL
			Dim [to] As String = "<" + toUser.Email + ">"
			Dim subj As String = Constants.NOTIFY_NEW_GROUP_BLOG_SUBJECT

			' Blog sender user name
			Dim blogSender As User = be.Sender
			Dim body As String = Constants.NOTIFY_NEW_GROUP_BLOG_BODY.Replace(Constants.TXT_USER_NAME_TAG, blogSender.Name)

			' Blog link
			body = body.Replace(Constants.TXT_BLOG_LINK_TAG, ComposeBlogLink(be) + "&" + Users.ComposeURLLoginParams(toUser))

			' No SPAM disclaimer
			body = body.Replace(Constants.TXT_DISCLAIMER_TEXT_TAG, Users.ComposeNoSPAMDisclaimer(toUser))

			' Do send the mail
			Utils.SendMail(from, [to], subj, body)
		End Sub

		Protected Shared Function ComposeBlogLink(ByVal be As GroupBlog) As String
			Return Constants.APPLICATION_URL + "/" + Constants.VIEW_BLOG_COMMENTS + "?id=" + be.ParentBlogID + "&" + Constants.GROUP_PARAM + "=" + be.GroupID
		End Function

	End Class
End Namespace






